/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.jsysacc.asistencia.model.dao.component.maestros;

import com.cerd.util.BaseSP01.JUtilBaseSP01;
import com.jsysacc.asistencia.model.beans.maestros.Personal;
import com.jsysacc.asistencia.model.dao.design.maestros.IPersonalDAO;
import com.jsysacc.asistencia.model.dao.ds.Cnx;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import org.joda.time.DateTime;

/**
 *
 * @author Carlos
 */
public class PersonalDAO extends JUtilBaseSP01 implements IPersonalDAO {

    private Connection cnx = null;
    private PreparedStatement pst = null;
    private Statement stm = null;
    private ResultSet rs = null;
    private CallableStatement cs = null;

    @Override
    public boolean insert(Personal entity) throws SQLException {
        cnx = Cnx.getConnection();
        cnx.setAutoCommit(false);
        try {
            String max = null;
            int maximo = 0;
            String codigo = null;

            String sql0 = "select max(substring(cod_per,3)) as maximo from " + Cnx.getSchema() + ".t_personal";

            pst = cnx.prepareStatement(sql0);
            rs = pst.executeQuery();
            if (rs.next()) {
                max = rs.getString("maximo");
            }

            if (_vacio(max)) {
                max = "0";
            }

            maximo = toInt(max) + 1;

            codigo = "PE" + PADL(toString(maximo), 3, '0');

            String sql = " INSERT INTO " + Cnx.getSchema() + ".t_personal(\n"
                    + "            cod_per, cod_tip_doc, num_ide, ape_pat, ape_mat, pri_nom, seg_nom, \n"
                    + "            fec_nac, sex_per, est_civ, mail_per, tel_per, cel_per, id_ubigeo, \n"
                    + "            cod_via, cod_zon, dir_per, zon_per, observ, cod_are, cod_car, \n"
                    + "            cod_hor, fec_ing, remuner, cod_ces, mot_ces, fec_ces, estado, \n"
                    + "            usu_reg, fec_reg)\n"
                    + "    VALUES (?, ?, ?, ?, ?, ?, ?, \n"
                    + "            ?, ?, ?, ?, ?, ?, ?, \n"
                    + "            ?, ?, ?, ?, ?, ?, ?, \n"
                    + "            ?, ?, ?, ?, ?, ?, ?, \n"
                    + "            ?, ?); ";

            pst = cnx.prepareStatement(sql);
            int pos = 1;
            pst.setString(pos++, codigo);
            pst.setString(pos++, entity.getCod_tip_doc());
            pst.setString(pos++, entity.getNum_ide());
            pst.setString(pos++, entity.getApe_pat());
            pst.setString(pos++, entity.getApe_mat());
            pst.setString(pos++, entity.getPri_nom());
            pst.setString(pos++, entity.getSeg_nom());
            pst.setDate(pos++, toDateTimeSQL(entity.getFec_nac()));
            pst.setString(pos++, entity.getSex_per());
            pst.setString(pos++, entity.getEst_civ());
            pst.setString(pos++, entity.getMail_per());
            pst.setString(pos++, entity.getTel_per());
            pst.setString(pos++, entity.getCel_per());
            pst.setInt(pos++, entity.getId_ubigeo());
            pst.setString(pos++, entity.getCod_via());
            pst.setString(pos++, entity.getCod_zon());
            pst.setString(pos++, entity.getDir_per());
            pst.setString(pos++, entity.getZon_per());
            pst.setString(pos++, entity.getObserv());
            pst.setString(pos++, entity.getCod_are());
            pst.setString(pos++, entity.getCod_car());
            pst.setString(pos++, entity.getCod_hor());
            pst.setDate(pos++, toDateTimeSQL(entity.getFec_ing()));
            pst.setDouble(pos++, entity.getRemuner());
            pst.setString(pos++, entity.getCod_ces());
            pst.setString(pos++, entity.getMot_ces());
            pst.setDate(pos++, toDateTimeSQL(entity.getFec_ces()));
            pst.setString(pos++, entity.getEstado());
            pst.setString(pos++, entity.getUsu_reg());
            pst.setTimestamp(pos++, toTimestampSQL(DateTime.now()));

            if (pst.executeUpdate() == 0) {
                throw new SQLException("ERROR AL INSERTAR EL REGISTRO");
            }
            cnx.commit();
            return true;
        } catch (SQLException ex) {
            cnx.rollback();
            throw new SQLException(ex.getMessage());
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }
    }

    @Override
    public boolean update(Personal entity) throws SQLException {
        String sql = " UPDATE " + Cnx.getSchema() + ".t_personal\n"
                + "   SET  cod_tip_doc=?, num_ide=?, ape_pat=?, ape_mat=?, pri_nom=?, \n"
                + "       seg_nom=?, fec_nac=?, sex_per=?, est_civ=?, mail_per=?, tel_per=?, \n"
                + "       cel_per=?, id_ubigeo=?, cod_via=?, cod_zon=?, dir_per=?, zon_per=?, \n"
                + "       observ=?, cod_are=?, cod_car=?, cod_hor=?, fec_ing=?, remuner=?, \n"
                + "       cod_ces=?, mot_ces=?, fec_ces=?, estado=? \n"
                + " WHERE cod_per =  ? ";
        cnx = Cnx.getConnection();
        cnx.setAutoCommit(false);
        try {
            pst = cnx.prepareCall(sql);
            int pos = 1;

            pst.setString(pos++, entity.getCod_tip_doc());
            pst.setString(pos++, entity.getNum_ide());
            pst.setString(pos++, entity.getApe_pat());
            pst.setString(pos++, entity.getApe_mat());
            pst.setString(pos++, entity.getPri_nom());
            pst.setString(pos++, entity.getSeg_nom());
            pst.setDate(pos++, toDateTimeSQL(entity.getFec_nac()));
            pst.setString(pos++, entity.getSex_per());
            pst.setString(pos++, entity.getEst_civ());
            pst.setString(pos++, entity.getMail_per());
            pst.setString(pos++, entity.getTel_per());
            pst.setString(pos++, entity.getCel_per());
            pst.setInt(pos++, entity.getId_ubigeo());
            pst.setString(pos++, entity.getCod_via());
            pst.setString(pos++, entity.getCod_zon());
            pst.setString(pos++, entity.getDir_per());
            pst.setString(pos++, entity.getZon_per());
            pst.setString(pos++, entity.getObserv());
            pst.setString(pos++, entity.getCod_are());
            pst.setString(pos++, entity.getCod_car());
            pst.setString(pos++, entity.getCod_hor());
            pst.setDate(pos++, toDateTimeSQL(entity.getFec_ing()));
            pst.setDouble(pos++, entity.getRemuner());
            pst.setString(pos++, entity.getCod_ces());
            pst.setString(pos++, entity.getMot_ces());
            pst.setDate(pos++, toDateTimeSQL(entity.getFec_ces()));
            pst.setString(pos++, entity.getEstado());
            pst.setString(pos++, entity.getCod_per());

            if (pst.executeUpdate() == 0) {
                throw new SQLException("ERROR AL MODIFICAR EL REGISTRO");
            }
            cnx.commit();
            return true;
        } catch (SQLException ex) {
            cnx.rollback();
            throw new SQLException(ex.getMessage());
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }
    }

    @Override
    public boolean delete(String pk) throws SQLException {
        String sql = " delete from " + Cnx.getSchema() + ".t_personal where cod_per = ? ";
        cnx = Cnx.getConnection();
        cnx.setAutoCommit(false);
        try {
            pst = cnx.prepareStatement(sql);
            int pos = 1;
            pst.setString(pos++, pk);

            if (pst.executeUpdate() == 0) {
                throw new SQLException("ERROR AL ELIMINAR EL REGISTRO");
            }
            cnx.commit();
            return true;
        } catch (SQLException ex) {
            cnx.rollback();
            throw new SQLException(ex.getMessage());
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }
    }

    @Override
    public ArrayList<Personal> getAll() throws SQLException {
        ArrayList<Personal> lista = new ArrayList<>();
        Personal oPersonal = null;
        String sql = " select * from " + Cnx.getSchema() + ".sp_t_personal_get_all() ; ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                oPersonal = new Personal();
                mapearBeanForRS(rs, oPersonal);
                lista.add(oPersonal);
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return lista;
    }

    @Override
    public ArrayList<Personal> getByCriterio(String criterio, String valor) throws SQLException {
        ArrayList<Personal> lista = new ArrayList<>();
        Personal oPersonal = null;
        String sql = " select * from " + Cnx.getSchema() + ".sp_t_personal_get_by_criterio(?, ?) ; ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            pst.setString(1, criterio);
            pst.setString(2, valor);
            rs = pst.executeQuery();
            while (rs.next()) {
                oPersonal = new Personal();
                mapearBeanForRS(rs, oPersonal);
                lista.add(oPersonal);
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return lista;
    }

    @Override
    public Personal getByPk(String pk) throws SQLException {
        Personal oPersonal = null;
        String sql = " select * from " + Cnx.getSchema() + ".sp_t_personal_get_by_pk(?) ; ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            int pos = 1;
            pst.setString(1, pk);
            rs = pst.executeQuery();
            while (rs.next()) {
                oPersonal = new Personal();
                mapearBeanForRS(rs, oPersonal);
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return oPersonal;
    }

    @Override
    public boolean existeByNum_ide(String num_ide) throws SQLException {
        String sql = " select * from " + Cnx.getSchema() + ".t_personal WHERE num_ide = ? AND estado = 'A' ; ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            int pos = 1;
            pst.setString(1, num_ide);
            rs = pst.executeQuery();
            while (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return false;
    }

    @Override
    public Personal getByNum_ide(String num_ide) throws SQLException {
        Personal oPersonal = null;
        String sql = " select * from " + Cnx.getSchema() + ".t_personal WHERE num_ide = ? AND estado = 'A' ";
        cnx = Cnx.getConnection();
        try {
            pst = cnx.prepareStatement(sql);
            int pos = 1;
            pst.setString(1, num_ide);
            rs = pst.executeQuery();
            while (rs.next()) {
                oPersonal = new Personal();
                mapearBeanForRS(rs, oPersonal);
            }
        } catch (SQLException e) {
            throw new SQLException(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (cnx != null) {
                    cnx.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex.getMessage());
            }
        }

        return oPersonal;
    }

}
